from flask_restful import abort
from sqlalchemy.exc import IntegrityError
from redash import models, limiter
from redash.handlers.base import (
    BaseResource,
    require_fields,
)
import os
import datetime
import uuid
import json
from flask import Flask, flash, request, redirect, url_for, send_from_directory
from werkzeug.utils import secure_filename
from redash import settings
import subprocess
from subprocess import Popen, PIPE
import pandas as pd
# import sys
# import argparse
# from pywpsrpc.rpcwpsapi import (createWpsRpcInstance, wpsapi)
# from pywpsrpc.common import (S_OK, QtApp)

class Uploadfile(BaseResource):
    def post(self,sourcetype,sourceid,types):
        f = request.files['file']
        # // 截取文件类型;这里可以根据文件类型进行判断
        filetype=settings.FILEUPLOAD["filetype"]
        type = f.filename[(f.filename.rindex('.') + 1):];
        type = type.lower()
        if not (type in filetype):
            return {"code": "error", "msg": "该文件类型不支持上传"}
        # if not ('.' in f.filename and  f.filename.rsplit('.', 1)[1].lower() in {'txt', 'pdf', 'png', 'jpg', 'jpeg', 'gif','doc','docx'}):
        #     return {"code": "error", "msg": "该文件类型不支持上传"}
        size = len(f.read())
        maxsize = settings.FILEUPLOAD["maxsize"]
        if (size> maxsize * 1024 * 1024):
            return {"code": "error", "msg": "该文件" +{size/(1024*1024)}+"M,超过200M，不能上传"}
        # 当前文件所在路径
        path =settings.FILEUPLOAD["path"]
        date = datetime.datetime.now()
        ymdstr =  date.strftime("%Y%m%d")
        basepath = os.path.join(path, ymdstr)
        if not os.path.exists(basepath):
            os.makedirs(basepath)
        # // 重构文件名
        uid = str(uuid.uuid1())
        uid = uid.replace("-","")
        filename = uid + "." + type
        #filename = (str(uuid.uuid1())).replace("-","") + "." + type

        if os.path.isfile(os.path.join(basepath,filename)):
            return {"code": "error", "msg": "该文件已存在，不能上传","filename":"","url":""}
        # 一定要先创建该文件夹，不然会提示没有该路径
        upload_path = os.path.join(basepath, filename)
        # // 创建文件实例
        # mff = open(upload_path, 'ab')
        # mff.write(f.read())
        # mff.close()
        # 保存文件
        f.seek(0)
        f.save(upload_path)
        filename = os.path.join('static/file/',ymdstr, filename)
        try:
            attaches = models.Attaches.get_by_id(sourceid,sourcetype)
            ps = attaches.to_dict()
            annex = ps['annex']
            annex.append({'uid': ''+uid+'', 'name': ''+f.filename+'','types': ''+types+'', 'status': 'done','url':''+filename+''})
            params = dict(annex=json.loads(json.dumps(annex)))
            self.update_model(attaches, params)
            models.db.session.commit()
        except :
            attaches = models.Attaches(
            userid=self.current_user.id,
            org=self.current_org,
            type = sourcetype,
            annex =[{"uid": ""+uid+"", "name": ""+f.filename+"","types": ""+types+"", "status": "done","url":""+filename+""}],
            status =0,
            sourcetype =sourcetype,
            sourceid =sourceid,
            )
            try:
                models.db.session.add(attaches)
                models.db.session.commit()
            except :
                abort(400, message="附件上传失败.") 

        return {"code": "sucess","msg":"上传成功！","filename":filename,"url":""}


class Downloadfile(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        filename = req["filename"]
        filename = filename.replace("static/file/","")
        # 当前文件所在路径
        basepath = settings.FILEUPLOAD["path"]
        if not os.path.exists(basepath):
            os.makedirs(basepath)

        type = filename[(filename.rindex('.') + 1):];
        type = type.lower()
        
        if not os.path.isfile(os.path.join(basepath,filename)):
            if (type in 'doc,docx'):
                filename = filename[(filename.rindex('.') - 1):] +".pdf";
        if not os.path.isfile(os.path.join(basepath,filename)):
            return {"code": "error", "msg": "该文件不存在，不能下载"}
        # 一定要先创建该文件夹，不然会提示没有该路径
        upload_path = os.path.join(basepath, filename)
        # 下载文件
        # return send_from_directory(upload_path, filename, as_attachment=True)
        # if os.path.isfile(os.path.join(basepath, filename)):as_attachment=True
        return send_from_directory(basepath, filename,as_attachment=True)
         # raise exceptions.MyHttpNotFound('not found file')
        # 返回上传成功界面
        # return {"code": "sucess","msg":"上传成功！"}
class Removefile(BaseResource):
    def post(self,sourcetype,sourceid,uid):
        req = request.get_json(force=True)
        try:
            attaches = models.Attaches.get_by_id(sourceid,sourcetype)
            ps = attaches.to_dict()
            annex = json.loads(json.dumps(ps['annex'])) 
            for i in range(len(annex)):
                if annex[i]["uid"] == uid:
                    annex.pop(i)
                    break
            params = dict(annex=annex)
            self.update_model(attaches, params)
            models.db.session.commit()
        except :
            abort(400, message="删除失败.") 
        # 当前文件所在路径20210505改为不删除
        # basepath = os.path.join(settings.FILEUPLOAD["path"], filename)
        # if (os.path.exists(basepath)):
        #     os.remove(basepath)
        # else:
        #     return {"code": "error","msg":"文件不存在，删除失败！"}
        return {"code": "sucess","msg":"删除成功！"}
    
class ImportExcelResource(BaseResource):
    def post(self,tablename,openseapool):
        f = request.files['file']
        # 当前文件所在路径
        path =settings.FILEUPLOAD["path"]
        basepath = os.path.join(path, "excel")
        if not os.path.exists(basepath):
            os.makedirs(basepath)
        
        type = f.filename[(f.filename.rindex('.') + 1):];
        type = type.lower()
         # // 重构文件名
        uid = str(uuid.uuid1())
        uid = uid.replace("-","")
        filename = uid + "." + type
        upload_path = os.path.join(basepath, filename)
        if os.path.isfile(upload_path):
            os.remove()
        f.seek(0)
        f.save(upload_path)
        data = pd.read_excel(f,sheet_name=0,keep_default_na=False)
        rows= data.values
        colums= data.columns
        errmsg =""
        if "姓名" not in colums :
            errmsg= errmsg+ " 姓名 列不存在。"
        if "证书" not in colums :
            errmsg= errmsg+ " 证书 列不存在。"
        if "手机号" not in colums and  "电话" not in colums :
            errmsg= errmsg+ " 手机号 列不存在。"   
        if len(errmsg)>0 :
            return {"code": "error","message":errmsg}
        strsql=""
        nums =1;
        for row in rows:
            code = ""
            name = ""
            sex =""
            major =""
            certificate = ""
            location = ""
            mobile =""
            idcard =""
            wechat =""
            socialsecurity =""
            area =""
            maintainer =""
            signatory =""
            userid =""
            bytstatus =""

            schooled=""
            threeperson =""
            biddingexit = ""
            price = "0"
            frontmoney = "0"
            periodunit =""
            periods ="0"
            informationfee = "0"
            message =""
            contracttype =""
            signingtime =""
            expirationtime =""
            signamount ="0"
            contractcode =""
            certificatestatus =""
            accountname =""
            accountcode =""
            accountbank =""
            contacts = ""
            contactsmobile = ""

            for i in range(len(colums)) :
                if colums[i]=="编码":
                    code = str(row[i]).strip().replace("'","")
                elif colums[i]=="姓名":
                    name = str(row[i]).strip().replace("'","")
                elif colums[i]=="性别":
                    sex = str(row[i]).strip().replace("'","")
                elif colums[i]=="毕业专业":
                    major = str(row[i]).strip().replace("'","")
                elif colums[i]=="证书":
                    certificate =str(row[i]).strip().replace("'","")
                elif colums[i]=="地区":
                    location = str(row[i]).strip().replace("'","")
                elif colums[i]=="手机号" or colums[i]=="电话":
                    mobile = str(row[i]).strip().replace("'","")
                elif colums[i]=="身份证号":
                    idcard = str(row[i]).strip().replace("'","")
                elif colums[i]=="微信":
                    wechat = str(row[i]).strip().replace("'","")
                elif colums[i]=="社保情况":
                    socialsecurity =str(row[i]).strip().replace("'","")
                elif colums[i]=="社保地区":
                    area = str(row[i]).strip().replace("'","")
                elif colums[i]=="维护人":
                    maintainer = str(row[i]).strip().replace("'","")
                elif colums[i]=="签约人":
                    signatory =str(row[i]).strip().replace("'","")
                elif colums[i]=="录入人":
                    userid = str(row[i]).strip().replace("'","")
                elif colums[i]=="状态":
                    bytstatus =str(row[i]).strip().replace("'","")
                elif colums[i]=="学历":
                    schooled =str(row[i]).strip().replace("'","")
                elif colums[i]=="三类人员":
                    threeperson =str(row[i]).strip().replace("'","")
                elif colums[i]=="招标出场":
                    biddingexit =str(row[i]).strip().replace("'","")
                elif colums[i]=="预计金额":
                    price =str(row[i]).strip().replace("'","")
                    price = price if price.isdecimal() else "0"
                elif colums[i]=="定金":
                    frontmoney =str(row[i]).strip().replace("'","")
                    frontmoney = frontmoney if frontmoney.isdecimal() else "0"
                elif colums[i]=="期限单位":
                    periodunit =str(row[i]).strip().replace("'","")
                elif colums[i]=="期限":
                    periods =str(row[i]).strip()
                    periods = periods if periods.isdecimal() else "1"
                elif colums[i]=="信息费":
                    informationfee =str(row[i]).strip().replace("'","")
                    informationfee = informationfee if informationfee.isdecimal() else "0"
                elif colums[i]=="备注":
                    message =str(row[i]).strip().replace("'","")
                elif colums[i]=="签约类型":
                    contracttype =str(row[i]).strip().replace("'","")
                elif colums[i]=="签约时间":
                    signingtime =str(row[i]).strip().replace("'","")
                elif colums[i]=="到期时间":
                    expirationtime =str(row[i]).strip().replace("'","")
                elif colums[i]=="金额":
                    signamount =str(row[i]).strip().replace("'","")
                    signamount = signamount if signamount.isdecimal() else "0"
                elif colums[i]=="合同编码":
                    contractcode =str(row[i]).strip().replace("'","")
                elif colums[i]=="证书状态":
                    certificatestatus =str(row[i]).strip().replace("'","")
                elif colums[i]=="户名":
                    accountname =str(row[i]).strip().replace("'","")
                elif colums[i]=="账户":
                    accountcode =str(row[i]).strip().replace("'","")
                elif colums[i]=="开户行":
                    accountbank =str(row[i]).strip().replace("'","")
                elif colums[i]=="联系人":
                    contacts =str(row[i]).strip().replace("'","")
                elif colums[i]=="联系方式":
                    contactsmobile =str(row[i]).strip().replace("'","") 

            nums =nums+1
            if len(name)<1 :
                errmsg =errmsg +"第"+str(nums)+"行人员姓名不存在。"
            if len(certificate)<1 :
                errmsg =errmsg +"第"+str(nums)+"行证书不存在。"
            if len(mobile)<1 :
                errmsg =errmsg +"第"+str(nums)+"行手机号不存在。"
            if len(bytstatus)<1 :
                bytstatus ="洽谈中"
            if len(idcard)>0 :
                idcardcheck = models.Employee.check_employee_field(0,"idcard",idcard)
                if idcardcheck is not None:
                    errmsg =errmsg +"第"+str(nums)+"行身份证号已存在:"+idcardcheck[0]+",不能重复。"
            if len(mobile)>0 :
                mobilecheck = models.Employee.check_employee_field(0,"mobile",mobile)
                if mobilecheck is not None:
                    errmsg =errmsg +"第"+str(nums)+"手机号已存在:"+mobilecheck[0]+",不能重复."
            if len(code)>0 :
                codecheck = models.Employee.check_employee_field(0,"code",code)
                if codecheck is not None:
                    errmsg =errmsg +"第"+str(nums)+"行人员编号已存在:"+codecheck[0]+",不能重复。"

            strsql =strsql+""" insert into public.employee(org_id,bill_date,code,name,
                sex,major,certificate,
                location,mobile,idcard,
                socialsecurity,area,maintainer,
                signatory,userid,bytstatus,openseapool,
                schooled,threeperson,biddingexit,price,
                frontmoney,periodunit,periods,informationfee,message,
                contracttype,signingtime,expirationtime,
                excelin,signamount,contractcode,orgunitid,
                certificatestatus,accountname,accountcode,accountbank,wechat,contacts,contactsmobile)"""
            
            strsql =strsql+""" select """+str(self.current_org.id) + """,now(),"""
            if len(code)>0 :
                strsql =strsql+"""'"""+str(code)+"""',"""
            else :
                strsql =strsql+""" public.gaincode('employee','code',now(),"""+str(self.current_org.id)+""","""+str(openseapool)+"""),"""
            strsql =strsql+"""'"""+str(name)+"""',
                '"""+str(sex)+"""' as sex,string_to_array('"""+str(major)+"""', ',') as major,string_to_array('"""+str(certificate)+"""', ',') as certificate,
                '"""+str(location)+"""' as location,'"""+str(mobile)+"""' as mobile,'"""+str(idcard)+"""' as idcard,
                case when '"""+str(socialsecurity)+"""'='有' then 1 else 0 end as  socialsecurity,'"""+str(area)+"""' as area,"""
            
            if str(openseapool)=="0" :
                strsql =strsql+"""(select COALESCE(max(id),"""+str(self.current_user.id)+""") From public.users where name='"""+str(maintainer)+"""') as maintainer,"""
            else :
                strsql =strsql+"""(select COALESCE(max(id),0) From public.users where name='"""+str(maintainer)+"""') as maintainer,"""

            strsql =strsql+"""(select COALESCE(max(id),0) From public.users where name='"""+str(signatory)+"""') as signatory,
                (select COALESCE(max(id),"""+str(self.current_user.id)+""") From public.users where name='"""+str(userid)+"""') as userid,
                '"""+str(bytstatus)+"""' as bytstatus,"""+str(openseapool)+""",  """
            strsql =strsql+"""'"""+schooled +"""','"""+threeperson+"""','"""+biddingexit+"""','"""+price+"""',
                '"""+frontmoney+"""','"""+periodunit+"""','"""+periods+"""','"""+informationfee+"""','"""+message+"""',
                '"""+contracttype+"""',
                case when '"""+signingtime+"""'='' then null else to_date('"""+signingtime+"""', 'yyyy-mm-dd' ) end signingtime,
                case when '"""+expirationtime+"""'='' then null else to_date('"""+expirationtime+"""', 'yyyy-mm-dd' ) end expirationtime,
                1,'"""+signamount+"""','"""+contractcode+"""','"""+str(self.current_user.orgunit_id)+"""', """
            
            strsql =strsql+"""'"""+certificatestatus +"""','"""+accountname+"""','"""+accountcode+"""','"""+accountbank+"""','"""+wechat+"""',
            '"""+contacts +"""','"""+contactsmobile+"""' ; """
            
        if len(errmsg)>0 :
            return {"code": "error","message":errmsg}
        else :
            try:
                models.db.session.execute(strsql)
                models.db.session.commit()
                return {"code": "sucess","message":"导入成功，一共导入"+str(len(rows))+"条记录。"}
            except :
                return {"code": "error","message":"导入失败。"}
